#!/usr/bin/python
# -*- coding: UTF-8 -*-

from lxml import etree
import os, sys, string, getopt,datetime

bOneFile = 'N'
nss = {'a' : "attribute", 'c' : "collection", 'o' : "object" }
dicFindText = {'Code'        :'{%s}Code'         % nss['a'],
               'Name'        :'{%s}Name'         % nss['a'],
               'Comment'     :'{%s}Comment'      % nss['a'],
               'DataType'    :'{%s}DataType'     % nss['a'],
               'Mandatory'   :'{%s}Mandatory'    % nss['a'],
               'DefaultValue':'{%s}DefaultValue' % nss['a']
               }
def print_head(oObject, fp, TblOrView='TABLE'):
    aTableCode = oObject.findtext(dicFindText['Code'])
    aTableName = oObject.findtext(dicFindText['Name'])
    aTableComment = oObject.findtext(dicFindText['Comment'])
    if aTableCode:
        fp.write('-- ' + aTableCode)
    else:
        raise 'aTableCode in null'

    if aTableName:
        fp.write('-- ' + aTableName + '\n')
    if aTableComment:
        aTableComment = string.replace(aTableComment,'\n',' : ')
        fp.write('-- ' + aTableComment + '\n')

    #fp.write('CONNECT TO %s' % os.environ['DBNAME'] + ';\n' +
    #        'DROP   %s  ' % TblOrView.upper() + aTableCode + ';\n')
    if TblOrView.lower() == 'table':
        fp.write('CREATE %s  ' % TblOrView.upper() + aTableCode + ' (\n')
    else:
        fp.write('CREATE %s  ' % TblOrView.upper() + aTableCode +  '  as  (\n')

def print_tail(fp):
    fp.write(');\n')
    '''
    fp.write('COMMIT WORK;'    + '\n' +
            'CONNECT RESET;'  + '\n' +
            'TERMINATE;'      + '\n')
    '''
    fp.write('-' * 30 + '\n\n')

def parse_table(oTable, fp):
    print_head(oTable, fp, 'Table')

    dicColKeysRef = {}; strColKeyCd = ''; lstColKeysId = []; lstColKeyCd = []
    for oKey in oTable.xpath('.//c:Keys/o:Key', namespaces = nss):
        dicColKeysRef = parse_key(oKey)
        strColKeyCd = dicColKeysRef.keys()[0]
        lstColKeysId = dicColKeysRef.values()[0]

    for oCol in oTable.xpath('.//c:Columns/o:Column', namespaces = nss):
        s = parse_column(fp, oCol,lstColKeysId)
        if s: lstColKeyCd.append(s)

    if strColKeyCd:
        str = (' '*4 + 'constraint %s primary key(') % strColKeyCd
        fp.write(str)
        str = string.join(lstColKeyCd, ',')
        fp.write(str + ')\n')

    print_tail(fp)

def parse_key(oKey):
    strColKeyCode = oKey.findtext(dicFindText['Code'])
    strColKeyCode = 'P_' + strColKeyCode.upper()
    oKeyCols = oKey.xpath('.//c:Key.Columns/o:Column', namespaces = nss)
    lstColKeys = [ oColKey.get('Ref') for oColKey in oKeyCols if oColKey.attrib ]
    dicColKeys = dict([(strColKeyCode, lstColKeys)])
    return dicColKeys


def parse_column(fp, oCol,lstColKeysId):
        aColCode = oCol.findtext(dicFindText['Code'])
        aColName = oCol.findtext(dicFindText['Name'])
        aColDataType = oCol.findtext(dicFindText['DataType'])
        aColMandatory = oCol.findtext(dicFindText['Mandatory'])
        aColComment = oCol.findtext(dicFindText['Comment'])
        aColDefault = oCol.findtext(dicFindText['DefaultValue'])
        strColMandatory = ' '*7 +','
        if aColMandatory and int(aColMandatory) == 1:
            strColMandatory = 'NOT NULL'
        if aColComment is None:
            aColComment = ' '
        else:
            aColComment = string.replace(aColComment, '\n', ' ')
            aColComment = string.replace(aColComment, '    ', ' - ')
            if len(aColComment) > 40:
                aColComment = '\n'+' '*8+'--'+ aColComment

        if lstColKeysId.count(oCol.get('Id')):
            strPriKey = 'Y '
        else:
            strPriKey = '  '

        if aColDefault:
            if aColDataType[:4] == 'CHAR':
                aColDefault = '\'' + aColDefault +'\''
            strDefualt = ' default' +' '+ aColDefault
        else:
            strDefualt = ' '
        fp.write(' '*4 + aColCode + 
                 ' '*(30 - len(aColCode))     + aColDataType     +
                 ' '*(15 - len(aColDataType)) + strColMandatory  + strDefualt + ',' +
                 '--'+ strPriKey + aColName   +':'+ aColComment +'\n'
                )
        if strPriKey.find('Y') != -1:
            return aColCode

def parse_view(oView, fp):
    print_head(oView, fp, 'view')
    aViewSql = oView.findtext('.//{%s}View.SQLQuery' % nss['a'])
    lstViewSql = aViewSql.split(',')

    i = 0
    for oCol in oView.xpath('.//c:Columns/o:ViewColumn', namespaces = nss):
        aColCode = oCol.findtext(dicFindText['Code'])
        aColName = oCol.findtext(dicFindText['Name'])
        aColDataType = oCol.findtext(dicFindText['DataType'])
        aColComment = oCol.findtext(dicFindText['Comment'])
        if aColCode is None:
            continue
        if aColName is None:
            aColName = ' '
        if aColDataType is None:
            aColDataType = ' '
        if aColComment is None:
            aColComment = ' '
        else:
            aColComment = string.replace(aColComment, '\n', ' ')
            aColComment = string.replace(aColComment, '    ', ' - ')
            if len(aColComment) > 30:
                aColComment = '\n'+' '*8+'--'+ aColComment

        while i < len(lstViewSql):
            if lstViewSql[i].find(aColCode) != -1:
                strtmp = lstViewSql[i] +',--'+ aColDataType +':'+ aColName +':'+ aColComment
                lstViewSql[i] = strtmp
                break
            i += 1

    str = string.join(lstViewSql, '')
    fp.write(str + '\n')
    print_tail(fp)

def parse_package(fp,oPackage, FilePath):
    oTables = oPackage.xpath('.//c:Tables/o:Table', namespaces = nss)
    for oTable in oTables:
        parse_table(oTable, fp)

    oViews = oPackage.xpath('.//c:Views/o:View', namespaces = nss)
    for oView in oViews:
        parse_view(oView, fp)

def parse_main(fp, FilePath):
    oPackage = etree.parse(fp).xpath('.//c:Packages/o:Package', namespaces = nss)
    if bOneFile == 'Y':
        fileNewName = FilePath + 'all.sql'
        if os.path.exists(fileNewName):
            os.remove(fileNewName)
        fp = open(fileNewName, 'w+')
        fp.write("--author: Mr.wangpeng\n\n" )
        for package in oPackage:
            parse_package(fp,package, FilePath)
        fp.close()
    else:
        for package in oPackage:
            strCode = oPackage.findtext(dicFindText['Code'])
            fileNewName = FilePath + strCode.lower() + '.sql'
            if os.path.exists(fileNewName):
                os.remove(fileNewName)
            fp = open(fileNewName, 'w+')
            parse_package(fp,package, FilePath)
            fp.close()
    

if __name__ == '__main__':
    print '-- parse start --' + str(datetime.datetime.now())
    try:
        opts, args = getopt.getopt(sys.argv[1:], "haf:", ["help"])
    except getopt.GetoptError:
        print "argv error"
        sys.exit(1)

    for o, a in opts:
        if o in ("-h", "--help"):
            sys.exit(1)
        if o in ("-a"):
            bOneFile = 'Y'
        if o in ("-f"):
            fp_name = a

    if "fp_name" not in locals():
        fp_name = '/home/wp/jee/svn-doc/meta/database.pdm'

    try:
        fp = open(fp_name, "r")
    except:
        print "open xml failed";
        sys.exit()

    ilast = string.find(fp_name,'_')
    ExportFilePath = os.environ['APPHOME'] + '/sql/table/' + datetime.datetime.strftime(datetime.datetime.now(),'%Y%m%d') + '/'
    if os.path.exists(ExportFilePath) != True:
        os.mkdir(ExportFilePath)
    parse_main(fp, ExportFilePath)
    fp.close()
    print '-- parse over --' + str(datetime.datetime.now())

